# -*- coding: UTF-8 -*-
# cython: language_level=3
# oracledb.apilevel # 2 线程可以共享模块和连接对象。

import oracledb
import os

from config import ORCL_DB, ORCL_IP, ORCL_PASSWD, ORCL_PORT, ORCL_USER
from utils.log import log

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'  # 防止查出的中文乱码
oracledb.init_oracle_client()


class Singleton(object):
    def __new__(cls, *args, **kwargs):
        if not hasattr(cls, '_inst'):
            cls._inst = super(Singleton, cls).__new__(cls, *args, **kwargs)

        return cls._inst


class OracleDB(Singleton):
    def __init__(self, ip=ORCL_IP, port=ORCL_PORT, db=ORCL_DB, user_name=ORCL_USER, user_pass=ORCL_PASSWD):
        super(OracleDB, self).__init__()

        if not hasattr(self, 'conn'):
            try:
                self.conn = oracledb.connect(user=user_name, password=user_pass, host=ip, port=port, service_name=db)
                self.conn.autocommit = True

            except Exception as e:
                raise
            else:
                log.debug('连接到数据库 %s' % db)

    def find(self, sql, parameters=None, fetch_one=False):
        # result = []
        with self.conn.cursor() as cursor:
            if fetch_one:
                result = cursor.execute(sql, parameters).fetchone()
            else:
                result = cursor.execute(sql, parameters).fetchall()

        return result

    def add(self, sql, exception_callfunc='', parameters=None):
        with self.conn.cursor() as cursor:

            try:
                cursor.execute(sql, parameters)
                # self.conn.commit()
            except Exception as e:
                log.error(e)
                if exception_callfunc:
                    exception_callfunc(e)

                return False
            else:
                return True

    def update(self, sql, parameters=None):
        with self.conn.cursor() as cursor:

            try:
                cursor.execute(sql, parameters)
                # self.conn.commit()
            except Exception as e:
                log.error(e)
                return False
            else:
                return True

    def delete(self, sql):
        with self.conn.cursor() as cursor:

            try:
                cursor.execute(sql)
                # self.conn.commit()
            except Exception as e:
                log.error(e)
                return False
            else:
                return True

    def set_unique_key(self, table, key):
        with self.conn.cursor() as cursor:

            try:
                sql = 'alter table %s add unique (%s)' % (table, key)
                cursor.execute(sql)
                # self.conn.commit()

            except Exception as e:
                log.error(table + ' ' + str(e) + ' key = ' + key)
            else:
                log.debug('%s表创建唯一索引成功 索引为 %s' % (table, key))

    def close(self):
        self.conn.close()


if __name__ == '__main__':
    db = OracleDB()
    for url, in db.find('SELECT URL FROM "TBL_NEW_WEBSITE"'):
        if len(url) < 4:
            continue
        print(url)
